Excel project: Cleaning data for visualization

Part 1:

Highlight: empty rows, duplicates, spelling mistakes, Double whitespaces/missing spaces, Split department name

Row data:

1. Removed all empty rows from data

I used Filter to select all blacks then delete them

2. Removed duplicates

I used Conditional Formatting to highlight duplicates then delete them

3. Correct spelling

I used Spelling from Review tab to replace all spelling mistakes

4. Double whitespaces

I used Find and Replace to replace double whitespace with a single one instead.

5. Department names in one column

I used Flash fill from home/fill to concatenate both names in one column.

6. Visual

I sort Equipment numbers from highest to smaller and show only the first 20 results to highlight in a bar chart

Result:

Part 2: Create a PivotTable to analyze data

ROW DATA:

STEPS:

CTRL+SHIFT+ARROW DOWN to select the data faster

CTRL+T to create the table(with header)

Calculate Sum, Avg, min, max, count with AutoSum (or functions)

Create a PivotTable, sort by Row Labels to remove aggregated values

Sorting by “Sum of Equipment count” descending.

Create 2 more PivotTables

In the second PivotTable, add the Equipment class in rows(below the department)

In the third PivotTable, add the Equipment class in rows(above the department)

Result:

1. Count by Sum of Equipment

2. Sort by Department and further by Equipment Class

3. Sort by Equipment class and further by Departments

Part 3: Pivot Tables

Quantity Sold by Dealer ID

Profit by Dealer ID and Year

Sum of Profits for Hudson model cars by Dealer ID